package net.hn.hnms.biz.plan.mapper;

import net.hn.hnms.biz.plan.domain.EfficientTransmission;
import net.hn.hnms.biz.plan.domain.EnvironmentRanking;
import net.hn.hnms.biz.plan.domain.MineNumber;
import net.hn.hnms.biz.plan.domain.repertoryInfo;
import net.hn.hnms.biz.plan.request.OpencastAndUndergroundMiningReq;
import net.hn.hnms.biz.plan.vo.*;
import net.hn.hnms.biz.plan.vo.*;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@Mapper
public interface GroupStatisticMapper {


    PlanCompleteStatisticVO planComplete3(@Param("beginDate") Date beginDate, @Param("endDate") Date endDate, @Param("companyCode") String companyCode );


    @Select("SELECT execution_footage_output.mine_code  mine_code FROM execution_footage_output GROUP BY execution_footage_output.mine_code")
    List<String> selectMineCode();
    @Select("SELECT execution_footage_output.company_code  mine_code FROM execution_footage_output GROUP BY execution_footage_output.company_code")
    List<String> selectcompanyCode();


    PlanCompleteStatisticVO planComplete(@Param("beginDate") Date beginDate, @Param("endDate") Date endDate, @Param("companyCode") String companyCode);

    FootAgeVo peelCount(@Param("beginDate") Date beginDate, @Param("endDate") Date endDate, @Param("companyCode") String companyCode);

    @Select("<script>" +
            " select COALESCE(sum(month_plan),0) as planOutput,COALESCE(sum(month_quantity),0) as output ,COALESCE(sum(proved_output),0) as proved_output,\n" +
            "COALESCE(sum(today_quantity),0) as todayQuantity, "+
            "COALESCE(round((sum(month_quantity))::numeric/sum(month_plan)::numeric*100,2),0) as completionRate,\n" +
            " COALESCE(round((sum(month_quantity-month_plan))::numeric/sum(month_plan)::numeric*100,2),0)  as monthOverabundance, "+
            "COALESCE(sum(year_plan),0) as yearPlan,COALESCE(sum(year_quantity),0) as outputYear,\n" +
                "COALESCE(round((sum(year_quantity))::numeric/sum(year_plan)::numeric*100,2),0) as yearCompletionRate, \n" +
                "COALESCE(round((sum(year_quantity-year_plan))::numeric/sum(year_plan)::numeric*100,2),0) as yearOverabundance \n" +
            "from yield_production where data_date ::date <![CDATA[ = ]]>  to_date( #{endDate}, 'yyyy-MM-dd' )  \n" +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            " group by data_date " +
            "</script>")
    MonthYieldVo outputStatistic(@Param("endDate") Date endDate, @Param("companyCode") String companyCode );


    @Select({"<script>" +
            " select\n" +
            "\n" +
            "COALESCE(round((\n" +
            "\n" +
            "((\n" +
            "select sum(today_quantity) from yield_production where 1=1 " +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            " and data_date  ::date <![CDATA[ >= ]]> to_date(#{nowStartDate}, 'YYYY-MM-DD' ) and data_date  ::date <![CDATA[ <= ]]> to_date(#{nowEndDate}, 'YYYY-MM-DD' ))-\n" +
            "(\n" +
            "select sum(today_quantity) from yield_production where 1=1" +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            " and data_date  ::date <![CDATA[ >= ]]> to_date( #{lastYearStartDate}, 'YYYY-MM-DD' ) and data_date  ::date <![CDATA[ <= ]]> to_date( #{lastYearEndDate}, 'YYYY-MM-DD' )\n" +
            "))\n" +
            "\n" +
            ")::numeric/\n" +
            "\n" +
            "(select sum(today_quantity) from yield_production where  1=1 " +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            " and data_date  ::date <![CDATA[ >= ]]> to_date(#{lastYearStartDate}, 'YYYY-MM-DD' ) and data_date  ::date <![CDATA[ <= ]]> to_date(#{lastYearEndDate}, 'YYYY-MM-DD' ))\n" +
            "\n" +
            "::numeric*100,2),0) " +
            "</script>"})
    BigDecimal outputStatisticYoy(@Param("lastYearStartDate") Date lastYearStartDate,
                                    @Param("lastYearEndDate") Date lastYearEndDate,
                                    @Param("nowStartDate") Date nowStartDate,
                                    @Param("nowEndDate") Date nowEndDate,
                                    @Param("companyCode") String companyCode );

    @Select("<script>" +
            "  select yp.mine_code ,yp.company_code, COALESCE(sum(gpc.proved_output),0) as provedOutput," +
            " COALESCE(round(( sum(today_quantity) )::numeric/10000::numeric,2),0) as todayQuantity, " +
            " COALESCE(ROUND((SUM(year_quantity - year_plan)::NUMERIC / NULLIF(SUM(year_plan), 0)::NUMERIC * 100), 2), 0) as yearPlan,\n" +
            " COALESCE(round((sum((month_quantity/10000)-(month_plan/10000)))::numeric,2),0) as monthOverabundance, " +
            " COALESCE(round(( sum(year_plan) )::numeric/10000::numeric,2),0)  as planOutPuts,\n" +
            "  COALESCE(round(( sum(month_plan) )::numeric/10000::numeric,2),0)  as planOutput," +
            " COALESCE(round(( sum((year_quantity/10000)-(year_plan/10000)) )::numeric,2),0)  as yearOverabundance,\n" +
            " ROUND(COALESCE((SUM(year_quantity)::numeric / 10000), 0), 2)  as yearCumulativeCompletion," +
            " COALESCE((SUM(year_quantity) / 10000.0)::TEXT, '0.00')   as yearOutput," +
            "    CASE     WHEN EXTRACT(DAY FROM CURRENT_DATE) = 1 THEN\n" +
            " COALESCE(ROUND((SUM(month_plan) / EXTRACT(DAY FROM (DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '1 DAY')::DATE))* 1/ 10000, 2), 0) \n" +
            "                   ELSE " +
            "   COALESCE(ROUND((SUM(month_plan) / EXTRACT(DAY FROM DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month') - DATE_TRUNC('month', CURRENT_DATE)))* 1/ 10000, 2), 0)  END AS planOutputDay, "+
            " COALESCE(round(( sum(month_quantity) )::numeric/10000::numeric,2),0)  as output" +
            "    from yield_production  " +
            " yp  left join " +
            "  group_production_capacity gpc  on yp.company_code=gpc.company_code " +
            "  and yp.mine_code=gpc.mine_code " +
            " where data_date  ::date =  to_date( #{endDate}, 'yyyy-MM-dd' )  \n" +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  yp.company_code = #{companyCode} \n" +
            "            </if>" +
            "               group by yp.mine_code ,yp.company_code\n" +
            "</script>")
    List<MonthYieldVo> outputStatisticDetailed (@Param("endDate") Date endDate,
                                  @Param("companyCode") String companyCode );
//    @Select("<script>" +
//            "  \t select mine_code ,company_code," +
//            " COALESCE(round((sum((month_quantity/10000)-(month_plan/10000)))::numeric,2),0) as monthOverabundance,\n" +
//            "            COALESCE(round(( sum(month_plan) )::numeric/10000::numeric,2),0)  as planTotal,\n" +
//            "             COALESCE(round(( sum(month_quantity) )::numeric/10000::numeric,2),0)  as completeTotal\n" +
//            "                from yield_production  where data_date  ::date =  to_date( '2023-04-02', 'yyyy-MM-dd' )  \n" +
//            "                          group by mine_code ,company_code \n" +
//            "</script>")
//    List<PlanStatisticVO> outputStatisticDetailedOfMonth (@Param("endDate") Date endDate,
//                                                   @Param("companyCode") String companyCode );


    @Select("<script>" +
            "\t\t\t\t\t\tselect\n" +
            "\t\t\t\t\t\tCOALESCE(round((sum(today_quantity-lasttoday_quantity))::numeric/sum(lasts)::numeric*100,2),0) as YearYoy,a.mine_code\n" +
            "\t\t\t\t\t\tfrom ( select sum(today_quantity) as today_quantity,mine_code,company_code from yield_production where 1=1  \n" +
            "                 \n" +
            "                AND  today_quantity !='0' \n" +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            "             and data_date  ::date  <![CDATA[ >= ]]>  to_date(#{nowStartDate}, 'YYYY-MM-DD' ) and data_date  ::date  <![CDATA[ <= ]]>  to_date(#{nowEndDate}, 'YYYY-MM-DD' ) group by mine_code,company_code) a \n" +
            "\t\t\t\t\t\t left join \n" +
            "\t\t\t\t\t\t (\n" +
            "\t\t\t\t\t\t\t\t select sum(today_quantity) as lasttoday_quantity, mine_code,company_code from yield_production where 1=1 " +
            "                  \n" +

            "                AND  today_quantity !='0' \n" +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            "             and data_date  ::date  <![CDATA[ >= ]]>  to_date( #{lastYearStartDate}, 'YYYY-MM-DD' ) and data_date  ::date  <![CDATA[ <= ]]>  to_date( #{lastYearEndDate}, 'YYYY-MM-DD' ) \tgroup by mine_code,company_code\n" +
            "\t\t\t\t\t\t ) b on a.mine_code = b.mine_code\n" +
            "\t\t\t\t\t\t left join\n" +
            "\t\t\t\t\t\t (\n" +
            "\t\t\t\t\t\t select sum(today_quantity) as lasts,mine_code,company_code from yield_production where  1=1  \n" +
            "                      \n" +

            "                AND  today_quantity !='0' \n" +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            "             and data_date  ::date  <![CDATA[ >= ]]>  to_date(#{lastYearStartDate}, 'YYYY-MM-DD' ) and data_date  ::date  <![CDATA[ <= ]]>  to_date(#{lastYearEndDate}, 'YYYY-MM-DD' ) \tgroup by mine_code,company_code\n" +
            "\t\t\t\t\t\t ) c on b.mine_code = c.mine_code\n" +
            "\t\t\t\t\t\t group by a.mine_code " +
            "</script>")
    List<MonthYieldVo> outputStatisticYoyDetailed (@Param("lastYearStartDate") Date lastYearStartDate,
                                                      @Param("lastYearEndDate") Date lastYearEndDate,
                                                      @Param("nowStartDate") Date nowStartDate,
                                                      @Param("nowEndDate") Date nowEndDate,
                                                   @Param("companyCode") String companyCode );

    @Select("<script>" +
            " select \n" +
            " COALESCE(sum(month_plan),0) ,COALESCE(sum(month_quantity),0) ,COALESCE(sum(gpc.proved_output),0) as provedOutput" +
            ",COALESCE(round((sum(month_quantity))::numeric/sum(month_plan)::numeric*100,2),0) ,\n" +
            "COALESCE(sum(year_plan),0) as planTotal,COALESCE(sum(year_quantity),0) as completeTotal,\n" +
            "COALESCE(round((sum(year_quantity))::numeric/sum(year_plan)::numeric*100,2),0) as percent,yp.company_code as dataCode\n" +
            "from yield_production " +
            " yp  left join " +
            "  group_production_capacity gpc  on yp.company_code=gpc.company_code " +
            "  and yp.mine_code=gpc.mine_code " +
            " where data_date  ::date <![CDATA[ = ]]>  to_date( '2023-03-06', 'yyyy-MM-dd' ) " +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  yp.company_code = #{companyCode} \n" +
            "            </if>" +
            "group by yp.company_code " +
            "</script>")
    List<PlanStatisticVO>  outputOfYearStatisticDetailed(@Param("endDate") Date endDate, @Param("companyCode") String companyCode );


    @Select("<script>" +
            " select COALESCE(sum(prv_quantity)) as lastDayOutPut,\n" +
            " COALESCE(round((sum(month_plan))::numeric/#{day}::numeric,2),0) ,COALESCE(sum(month_quantity),0) as monthOutput ,\n" +
            " COALESCE(sum(month_plan),0) as monthPlan,\n" +
            "COALESCE(round((sum(month_quantity))::numeric/sum(month_plan)::numeric*100,2),0) as percent,\n" +
            "COALESCE(round((sum(month_quantity-month_plan))::numeric/sum(month_plan)::numeric*100,2),0) as monthOverabundance,\n" +
            "COALESCE(sum(year_plan),0) as planTotal,COALESCE(sum(year_quantity),0) as completeTotal,\n" +
            "COALESCE(round((sum(year_quantity))::numeric/sum(year_plan)::numeric*100,2),0) ,mine_code as dataCode\n" +
            "from yield_production  where data_date  ::date <![CDATA[ = ]]>  to_date( #{endDate}, 'yyyy-MM-dd' ) " +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            " and month_plan!='0'  group by mine_code " +
            "</script>")
    List<PlanStatisticVO>  outputOfYearStatisticDetailedOfMineCode(@Param("endDate") Date endDate,@Param("day") int day,@Param("companyCode") String companyCode );

    @Select("<script>" +
            " select COALESCE(sum(prv_quantity)) as lastDayOutPut,\n" +
            " COALESCE(sum(month_plan),0) as planTotal,COALESCE(sum(month_quantity),0) as completeTotal,\n" +
            "COALESCE(round((sum(month_quantity))::numeric/sum(month_plan)::numeric*100,2),0) as percent,\n" +
            "COALESCE(round((sum(month_quantity-month_plan))::numeric/sum(month_plan)::numeric*100,2),0) as monthOverabundance,\n" +
            "COALESCE(sum(year_plan),0) ,COALESCE(sum(year_quantity),0),\n" +
            "COALESCE(round((sum(year_quantity))::numeric/sum(year_plan)::numeric*100,2),0) ,company_code as dataCode\n" +
            "from yield_production  where data_date  ::date <![CDATA[ = ]]>  to_date( #{endDate}, 'yyyy-MM-dd' ) " +
            "            <if test='companyCode!= null and companyCode != \"\"'>" +
            "                AND  company_code = #{companyCode} \n" +
            "            </if>" +
            "group by company_code " +
            "</script>")
    List<PlanStatisticVO>  outputOfMonthStatisticDetailed(@Param("endDate") Date endDate,@Param("companyCode") String companyCode );

    /**
     * 去年产量
     * @param lastYearStartDate
     * @param lastYearEndDate
     * @param code
     * @return
     */
    @Select("<script>" +
            " select sum(today_quantity) from yield_production where data_date  ::date  <![CDATA[ >= ]]>     to_date( #{lastYearStartDate}, 'yyyy-MM-dd' ) " +
            " and data_date ::date  <![CDATA[ <= ]]>      to_date( #{lastYearEndDate}, 'yyyy-MM-dd' )   " +
            " " +
            "            <if test='type== 1 and code!= null and code != \"\"'>" +
            "                AND  company_code = #{code} \n" +
            "            </if>" +
            "            <if test='type== 2 and code!= null and code != \"\"'>" +
            "                AND  mine_code = #{code} \n" +
            "            </if>" +
            "</script>")
    BigDecimal  lastYearOutputStatisticSituation(@Param("lastYearStartDate") Date lastYearStartDate,
                                                            @Param("lastYearEndDate") Date lastYearEndDate,
                                                            @Param("type") Integer type,
                                                            @Param("code") String code );

    /**
     *  产量情况详情 字段包含去年产量和今年计划
     * @param lastYearStartDate
     * @param lastYearEndDate
     * @param nowYearStartDate
     * @param type
     * @return
     */
    @Select("<script>" +
            "  select COALESCE(round(( sum(a.today_quantity) )::numeric/10000::numeric,2),0)  as lastYearOutPut,sum(c.provedOutput) as proved_output,a.mine_code,a.company_code,COALESCE(round(( sum(b.year_plan) )::numeric/10000::numeric,2),0)   as nowYearPlan," +
            "  case when  a.type='1' then '露天矿'  else '井工矿' end as typeName from (" +
            " select sum(today_quantity) as today_quantity,mine_code,type,company_code from yield_production where data_date  ::date   <![CDATA[ >= ]]>     to_date( #{lastYearStartDate}, 'yyyy-MM-dd' ) " +
            "             and data_date ::date  <![CDATA[ <= ]]>     to_date( #{lastYearEndDate}, 'yyyy-MM-dd' ) group by mine_code,type,company_code )as a" +
            " left join" +
            " ( select sum(year_plan) as year_plan,sum(proved_output) as provedoutput,mine_code,type,company_code from  yield_production where data_date  ::date   <![CDATA[ = ]]>      to_date( #{nowYearStartDate}, 'yyyy-MM-dd' ) group by mine_code,type,company_code)  b   on a.mine_code = b.mine_code " +
            "  left join  " +
            "  (select sum(proved_output) as provedOutput,company_code,mine_code from group_production_capacity  group by mine_code,company_code) c on  a.mine_code = c.mine_code " +
            "" +
            " GROUP BY a.mine_code,a.company_code,a.type " +
            "</script>")
    //nowYearStartDate
    List<PlanStatisticVO> lastYearAndNowYearOutputStatisticSituation(@Param("lastYearStartDate") Date lastYearStartDate,
                                                                     @Param("lastYearEndDate") Date lastYearEndDate,
                                                                     @Param("nowYearStartDate") Date nowYearStartDate,
                                                                     @Param("type") Integer type);


    /**
     * 去年计划
     * @param lastYearStartDate
     * @param code
     * @return
     */
    @Select("<script>" +
            " select sum(year_plan) from  yield_production where data_date  ::date  <![CDATA[ = ]]>    to_date( #{lastYearStartDate}, 'yyyy-MM-dd' )  " +
            "            <if test='type ==  1 and code!= null and code != \"\"'>" +
            "                AND  company_code = #{code} \n" +
            "            </if>" +
            "            <if test='type ==  2 and code!= null and code != \"\"'>" +
            "                AND  mine_code = #{code} \n" +
            "            </if>" +
            "</script>")
    BigDecimal  lastYearOutputPlanStatisticSituation(@Param("lastYearStartDate") Date lastYearStartDate,   @Param("type") Integer type,
                                                            @Param("code") String code );
    /**
     * 今年计划
     * @param nowYearStartDate
     * @param code
     * @return
     */
    @Select("<script>" +
            " select sum(year_plan) from  yield_production where data_date  ::date <![CDATA[ = ]]>     to_date( #{nowYearStartDate}, 'yyyy-MM-dd' )  " +
            "            <if test=' type ==  1 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} \n" +
            "            </if>" +
            "            <if test=' type ==  2 and code!= null and code != \"\" '>" +
            "                AND  mine_code = #{code} \n" +
            "            </if>" +
            "</script>")
    BigDecimal  nowYearOutputPlanStatisticSituation(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                                            @Param("code") String code );


    @Select("<script>" +
            "  select sum(proved_output) as provedOutput,sum(wellProvedOutput) as dewProvedOutput ,sum(dewProvedOutput) as wellProvedOutput from (" +
            "  select  sum(proved_output) as  proved_output, " +
            " case when type='1' then sum(proved_output)  else 0  end as wellProvedOutput , " +
            " case when type='2' then sum(proved_output)  else 0  end as dewProvedOutput  " +
            " from group_production_capacity where 1=1  " +
            "            <if test=' type ==  1 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} \n" +
            "            </if>" +
            "            <if test=' type ==  2 and code!= null and code != \"\" '>" +
            "                AND  mine_code = #{code} \n" +
            "            </if>" +
            " group by type) as a " +

            "</script>")
    PlanStatisticVO  nowYearProvedOutputPlanStatisticSituation(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                                    @Param("code") String code );


    /**
     * zuo
     * @param nowYearStartDate
     * @param code
     * @return
     */
    @Select("<script>" +
            " select " +
            " case when type='1' then '露天矿'  else '井工矿' end as typeName," +
            "            <if test='type ==  2  '>" +
            "                company_code,mine_code,  " +
            "            </if>" +
            "            <if test='type ==  3  '>" +
            "                company_code,  " +
            "            </if>" +
            " sum(today_quantity) as laseDayOutput,COALESCE(round((sum(month_plan))::numeric/#{daysOfMonth}::numeric,2),0) as planTotal from yield_production " +
            " where data_date  ::date  <![CDATA[ = ]]>    to_date( #{nowYearStartDate}, 'yyyy-MM-dd' )  " +
            "            <if test='type ==  1 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            " GROUP BY type  " +
            "            <if test='type ==  2  '>" +
            "                ,company_code,mine_code  " +
            "            </if>" +
            "            <if test='type == 3  '>" +
            "                ,company_code  " +
            "            </if>" +
            "</script>")
    List<PlanStatisticVO>  laseDayOutputAndPlanStatisticSituation(@Param("nowYearStartDate") Date nowYearStartDate,
                                                            @Param("daysOfMonth") Integer daysOfMonth,
                                                            @Param("type") Integer type,
                                                            @Param("code") String code );
    @Select("<script>" +
            " select " +
            " case when type='1' then '露天矿'  else '井工矿' end as typeName, " +

            "            <if test='type ==  2  '>" +
            "                company_code,mine_code,  " +
            "            </if>" +
            "            <if test='type ==  3  '>" +
            "                company_code,  " +
            "            </if>" +
            " sum(prv_quantity) as laseDayOutput,COALESCE(round((sum(month_plan))::numeric/#{daysOfMonth}::numeric,2),0) as planTotal from yield_production " +
            " where data_date  ::date  <![CDATA[ = ]]>    to_date( #{nowYearStartDate}, 'yyyy-MM-dd' )  " +
            "            <if test='type ==  2 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            " GROUP BY type,  " +
            "            <if test='type ==  2  '>" +
            "                company_code,mine_code  " +
            "            </if>" +
            "            <if test='type == 3  '>" +
            "                company_code  " +
            "            </if>" +
            "</script>")
    List<PlanStatisticVO>  laseDayOutputAndPlanStatisticSituationByMine(@Param("nowYearStartDate") Date nowYearStartDate,
                                                                  @Param("daysOfMonth") Integer daysOfMonth,
                                                                  @Param("type") Integer type,
                                                                  @Param("code") String code );

    @Select("<script>" +
            " select COALESCE(sum(peel_amount),0) as completeTotal " +

            "from execution_peel where 1=1 and execution_date = #{nowYearStartDate} " +
            "            <if test='type ==  1 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            "</script>")
    BigDecimal  laseDayPeelStatistic(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                                    @Param("code") String code );

    @Select("<script>" +
            " select COALESCE(sum(footage),0) as completeTotal " +

            "from execution_footage_output where 1=1 and  work_face_type = '1' and del_flag='0' and execution_date = #{nowYearStartDate} " +
            "            <if test='type ==  1 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            "</script>")
    BigDecimal  laseDayFootageStatistic(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                     @Param("code") String code );

    @Select("<script>" +
            " select COALESCE(sum(drivage_plan_footage),0) from  plan_drivage where drivage_plan_type = '0' and del_flag ='0' " +

            " and plan_start_date = #{nowYearStartDate} " +
            "            <if test='type ==  1 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            "</script>")
    BigDecimal  laseDayPlanFootageStatistic(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                        @Param("code") String code );

    @Select("<script>" +
            " select COALESCE(sum(peel_amount),0) as completeTotal,company_code " +

            "from execution_peel where 1=1 and execution_date = #{nowYearStartDate} " +
            "            <if test='type ==  3 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            " GROUP BY company_code "+
            "</script>")
    List<PlanStatisticVO>  laseDayPeelStatisticByCompany(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                     @Param("code") String code );
    @Select("<script>" +
            " select COALESCE(sum(peel_amount),0) as completeTotal,company_code,mine_code " +

            "from execution_peel where 1=1 and execution_date = #{nowYearStartDate} " +
            "            <if test='type ==  2 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            " GROUP BY company_code,mine_code "+
            "</script>")
    List<PlanStatisticVO>  laseDayPeelStatisticByMine(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                     @Param("code") String code );

    @Select("<script>" +
            " select COALESCE(sum(\"output\"),0) as lastDayFootage, COALESCE(sum(\"footage\"),0) as footage,company_code,mine_code  from execution_footage_output where work_face_type = '1' and del_flag='0' " +

            "  and   execution_date = #{nowYearStartDate} " +
            "            <if test='type ==  2 and code!= null and code != \"\" '>" +
            "                AND  company_code = #{code} " +
            "            </if>" +
            " GROUP BY company_code,mine_code "+
            "</script>")
    List<PlanStatisticVO>  laseDayFootageStatisticByMine(@Param("nowYearStartDate") Date nowYearStartDate,  @Param("type") Integer type,
                                                      @Param("code") String code );





//total实际  total2年计划 name同比 total3 计划超欠
    @Select("<script>" +
            " select a.year_quantity_sum as total,b.yearplan as total2,c.yoy as name,\n" +
            " COALESCE(round((a.year_quantity_sum-b.yearplan)::numeric/b.yearplan::numeric*100,2),0)  as total3\n" +
            " from (select year_quantity_sum,data_date from yield_ji_tuan_production where data_date = (select max(data_date) from yield_ji_tuan_production) ) as a\n" +
            " left join \n" +
            " (\n" +
            " select sum(year_plan) as yearplan,data_date from yield_year_plan  \n" +
            " where data_date = (select max(data_date) from yield_year_plan )\n" +
            " group by data_date \n" +
            " ) b on a.data_date = b.data_date\n" +
            " left join yield_production c on a.data_date = c.data_date limit  1 " +
            "</script>")
    MonthValue annualSituation(@Param("companyCode") String companyCode);


    List<MiningContinuationVo> faceCondition(@Param("companyCode") String companyCode, @Param("mineCode") String mineCode, @Param("type")Integer type);

    SaleAnalyseVo saleAnalyse(@Param("beginDate") Date beginDate, @Param("endDate") Date endDate, @Param("companyCode") String companyCode);

    SaleAnalyseVo yearSaleAnalyse(@Param("companyCode") String[] companyCode);

    SaleAnalyseVo monthSaleAnalyse(@Param("companyCode") String[] companyCode);

    SaleAnalyseVo prvSaleAnalyse(@Param("companyCode") String[] companyCode);
    SaleAnalyseVo prvSaleAnalyseTwo(@Param("companyCode") String[] companyCode);


    SaleAnalyseVo prvSaleAnalyseTen(@Param("companyCode") String[] companyCode);

    List<SaleAnalyseVo> yearSaleAnalyseMyAndMk(@Param("companyCode") String[] companyCode,@Param("type") String type);

    List<SaleAnalyseVo> monthSaleAnalyseMyAndMk(@Param("companyCode") String[] companyCode,@Param("type") String type);

    List<SaleAnalyseVo> prvSaleAnalyseMyAndMk(@Param("companyCode") String[] companyCode,@Param("type") String type);
    List<SaleAnalyseVo> prvSaleAnalyseMyAndMkTwo(@Param("companyCode") String[] companyCode,@Param("type") String type);


    List<SaleAnalyseVo> last30DaysVariationTrend(@Param("companyCode") String[] companyCode,@Param("type") String type);

    List<SaleAnalyseVo> last30DaysVariationTrendBeforeYesterday(@Param("companyCode") String[] companyCode,@Param("type") String type);

    List<SaleAnalyseVo> last12MonthsVariationTrend(@Param("companyCode") String[] companyCode,@Param("type") String type);

    List<SaleAnalyseVo> last10YearsVariationTrend(@Param("companyCode") String[] companyCode,@Param("type") String type);


    List<SaleAnalyseVo> saleAnalyseSubmenu(@Param("beginDate") Date beginDate, @Param("endDate") Date endDate,@Param("monthBeginDate") Date monthBeginDate, @Param("monthEndDate") Date monthEndDate, @Param("companyCode") String companyCode);

    List<ProductionAndMarketingVo> productionAndMarketingCount(@Param("companyCode") String companyCode);

    List<ProductionAndMarketingVo> productionAndMarketingCountSubmenu(@Param("beginDate") Date beginDate, @Param("endDate") Date endDate, @Param("companyCode") String companyCode);

    OpencastAndUndergroundMiningVo opencastAndUndergroundMiningCount(@Param("req") OpencastAndUndergroundMiningReq req);

    List<OpencastAndUndergroundMiningVo> opencastAndUndergroundMiningCountSubmenu(@Param("req") OpencastAndUndergroundMiningReq req);
    List<OpencastAndUndergroundMiningVo> opencastAndUndergroundMiningCountSubmenuTypeTwo(@Param("req") OpencastAndUndergroundMiningReq req);

    BigDecimal getGroupProvedOutput(@Param("companyCode") String companyCode);

    List<ExecutionFootageOutputVO> getFillingAnalysis(@Param("beginDate") String beginDate,@Param("endDate") String endDate);

    List<MineNumber> getMineNumber(@Param("companyCode") String companyCode);



    @Select("<script>" +
            " \n" +
            " SELECT \n" +
            "    to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY') AS yearMonth,\n" +
            "    companyName,\n" +
            "    ROUND(output, 0) as output\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "             CASE \n" +
            "                     WHEN company_code = '91150100767874571R' THEN '北方公司'\n" +
            "                  WHEN company_code = '911507001151964613' THEN '蒙东公司'\n" +
            "                  ELSE '煤业公司'\n" +
            "               END AS companyName,\n" +
            "        ROUND(SUM(year_quantity) / 10000, 0) AS output \n" +
            "    FROM \n" +
            "        yield_production\n" +
            "    WHERE \n" +
            "        date_trunc('year', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 YEAR - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "        \n" +
            "        AND data_date <![CDATA[ >= ]]>  TO_CHAR(NOW() - INTERVAL '10 YEARS', 'YYYY-MM-DD')\n" +
            "    GROUP BY \n" +
            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD'), companyName\n" +
            ") AS subquery1\n" +
            "UNION ALL\n" +
            "SELECT \n" +
            "    to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY') AS yearMonth,\n" +
            "    '总计' AS companyName,\n" +
            "    ROUND(SUM(output), 0) AS output\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "               CASE \n" +
            "                   WHEN company_code = '91150100767874571R' THEN '北方公司'\n" +
            "                  WHEN company_code = '911507001151964613' THEN '蒙东公司'\n" +
            "                  ELSE '煤业公司'\n" +
            "               END AS companyName,\n" +
            "        ROUND(SUM(year_quantity) / 10000, 0) AS output \n" +
            "    FROM \n" +
            "        yield_production\n" +
            "    WHERE \n" +
            "        date_trunc('year', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 YEAR - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "    \n" +
            "        AND data_date <![CDATA[ >= ]]> TO_CHAR(NOW() - INTERVAL '10 YEARS', 'YYYY-MM-DD')\n" +
            "    GROUP BY \n" +
            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD'), companyName\n" +
            ") AS subquery2\n" +
            "GROUP BY \n" +
            "    yearMonth\n" +
            "ORDER BY \n" +
            "    yearMonth, companyName; " +
            "</script>")
    List<MonthOuputVO> recentlyTenYearsByIndustry();
    @Select("<script>" +
            "            <if test='type== 1'>" +
            "  WITH DATA AS (\n" +
            "    SELECT\n" +
            "        st_year AS stYear,\n" +
            "         org_short_name \n" +
            "         AS org_short_name, \n" +
            "        ROUND(SUM(actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_year_total_d_f\n" +
            "    WHERE\n" +
            "        st_year::INTEGER <![CDATA[ >= ]]> EXTRACT(YEAR FROM CURRENT_DATE) - 11\n" +
            "        AND st_year::INTEGER <![CDATA[ < ]]> EXTRACT(YEAR FROM CURRENT_DATE)\n" +
            "        AND organization_type = 'MK'  and  org_code=#{mineCode} \n" +
            "    GROUP BY\n" +
            "        st_year,\n" +
            "        org_short_name  \n" +
            "        \n" +
            "), YEARLY_DATA AS (\n" +
            "    SELECT\n" +
            "        d.stYear,\n" +
            "        d.org_short_name,\n" +
            "        d.actualOutputQty,\n" +
            "        d.actualSalesQty,\n" +
            "        LAG(d.actualOutputQty) OVER (PARTITION BY d.org_short_name ORDER BY d.stYear) AS previousOutputQty,\n" +
            "        LAG(d.actualSalesQty) OVER (PARTITION BY d.org_short_name ORDER BY d.stYear) AS previousSalesQty\n" +
            "    FROM\n" +
            "        DATA d\n" +
            ")\n" +
            "SELECT\n" +
            "    stYear,\n" +
            "    org_short_name,\n" +
            "    actualOutputQty,\n" +
            "    previousOutputQty,\n" +
            "      TO_CHAR(COALESCE(CAST(\n" +
            "      ROUND(CASE \n" +
            "        WHEN previousOutputQty IS NOT NULL AND previousOutputQty <![CDATA[ <> ]]> 0 THEN \n" +
            "            ((actualOutputQty / previousOutputQty) - 1) * 100 \n" +
            "        ELSE '0' \n" +
            "    END, 2) \n" +
            "      AS numeric), 0), 'FM9999999990.00')\n" +
            "    AS yoyOutputGrowth,\n" +
            "      TO_CHAR(COALESCE(CAST(actualSalesQty AS numeric), 0), 'FM9999999990.00') AS actualSalesQty,\n" +
            "    previousSalesQty,\n" +
            "    TO_CHAR(COALESCE(CAST(\n" +
            "    ROUND(CASE \n" +
            "        WHEN previousSalesQty IS NOT NULL AND previousSalesQty <![CDATA[ <> ]]> 0 THEN \n" +
            "            ((actualSalesQty / previousSalesQty) - 1) * 100 \n" +
            "        ELSE '0' \n" +
            "    END, 2) \n" +
            "    \n" +
            "    AS numeric), 0), 'FM9999999990.00') \n" +
            "    AS yoySalesGrowth\n" +
            "FROM\n" +
            "    YEARLY_DATA where 1=1\n" +
            "ORDER BY\n" +
            "    stYear asc;\n " +
            "            </if>" +
            " " +
            "            <if test='type== 2'>" +
            "  WITH ALL_MONTHS AS (\n" +
            "    SELECT \n" +
            "        DATE_TRUNC('month', ADD_MONTHS(CURRENT_DATE, -n)) AS month_date\n" +
            "    FROM \n" +
            "        generate_series(0, 14) AS n  \n" +
            "), DATA AS (\n" +
            "    SELECT\n" +
            "        DATE_TRUNC('month', TO_DATE(st_month <![CDATA[ || ]]> '-01', 'YYYY-MM-DD')) AS stMonth,\n" +
            "        org_short_name, \n" +
            "        ROUND(SUM(actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS actualSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_month_total_d_f\n" +
            "    WHERE\n" +
            "        TO_DATE(st_month <![CDATA[ || ]]> '-01', 'YYYY-MM-DD') <![CDATA[ >= ]]> DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'  \n" +
            "        AND TO_DATE(st_month <![CDATA[ || ]]> '-01', 'YYYY-MM-DD') <![CDATA[ < ]]> DATE_TRUNC('month', CURRENT_DATE)  \n" +
            "        AND organization_type = 'MK'  AND org_code = #{mineCode} \n" +
            "    GROUP BY\n" +
            "        stMonth,\n" +
            "        org_short_name  \n" +
            "), LAST_YEAR_DATA AS (\n" +
            "    SELECT\n" +
            "        DATE_TRUNC('month', TO_DATE(st_month <![CDATA[ || ]]> '-01', 'YYYY-MM-DD')) AS lastYearMonth,\n" +
            "        org_short_name,\n" +
            "        ROUND(SUM(actual_output_qty) / 10000.0, 2) AS lastYearOutputQty,\n" +
            "        ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS lastYearSalesQty\n" +
            "    FROM\n" +
            "        dm.dm_jygl_coal_mining_month_total_d_f\n" +
            "    WHERE\n" +
            "        TO_DATE(st_month <![CDATA[ || ]]> '-01', 'YYYY-MM-DD') <![CDATA[ >= ]]> DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '24 months'  \n" +
            "        AND TO_DATE(st_month <![CDATA[ || ]]> '-01', 'YYYY-MM-DD') <![CDATA[ < ]]> DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'  \n" +
            "        AND organization_type = 'MK'  AND org_code = #{mineCode}  \n" +
            "    GROUP BY\n" +
            "        lastYearMonth,\n" +
            "        org_short_name\n" +
            "), MONTHLY_DATA AS (\n" +
            "    SELECT\n" +
            "        am.month_date AS stMonth,\n" +
            "        COALESCE(d.org_short_name, '') AS org_short_name,\n" +
            "        COALESCE(d.actualOutputQty, 0) AS actualOutputQty,\n" +
            "        COALESCE(ly.lastYearOutputQty, 0) AS previousOutputQty,\n" +
            "        COALESCE(d.actualSalesQty, 0) AS actualSalesQty,\n" +
            "        COALESCE(ly.lastYearSalesQty, 0) AS previousSalesQty,\n" +
            "        ROW_NUMBER() OVER (PARTITION BY d.org_short_name ORDER BY am.month_date) AS rn \n" +
            "    FROM\n" +
            "        ALL_MONTHS am\n" +
            "    LEFT JOIN\n" +
            "        DATA d ON am.month_date = d.stMonth\n" +
            "    LEFT JOIN\n" +
            "        LAST_YEAR_DATA ly ON d.stMonth = ADD_MONTHS(ly.lastYearMonth, 12) AND d.org_short_name = ly.org_short_name  \n" +
            ")\n" +
            "SELECT\n" +
            "    TO_CHAR(stMonth, 'YYYY-MM') AS stMonth,\n" +
            "    org_short_name,\n" +
            "    actualOutputQty,\n" +
            "    previousOutputQty,\n" +
            "    TO_CHAR(COALESCE(ROUND(\n" +
            "        CASE \n" +
            "            WHEN previousOutputQty IS NOT NULL AND previousOutputQty <![CDATA[ <> ]]> 0 THEN \n" +
            "                ((actualOutputQty / previousOutputQty) - 1) * 100 \n" +
            "            ELSE 0 \n" +
            "        END, 2), 0), 'FM9999999990.00') AS yoyOutputGrowth,\n" +
            "    actualSalesQty,\n" +
            "    previousSalesQty,\n" +
            "    TO_CHAR(COALESCE(ROUND(\n" +
            "        CASE \n" +
            "            WHEN previousSalesQty IS NOT NULL AND previousSalesQty <![CDATA[ <> ]]> 0 THEN \n" +
            "                ((actualSalesQty / previousSalesQty) - 1) * 100 \n" +
            "            ELSE 0 \n" +
            "        END, 2), 0), 'FM9999999990.00') AS yoySalesGrowth\n" +
            "FROM\n" +
            "    MONTHLY_DATA \n" +
            "WHERE \n" +
            "    rn <![CDATA[ > ]]> 1\n" +
            "     AND stMonth <![CDATA[ < ]]> DATE_TRUNC('month', CURRENT_DATE) \n" +
            "ORDER BY\n" +
            "    stMonth asc;\n  " +
            "            </if>" +
            "</script>")
    List<CoalMiningTotalVo> getOutputSaleYoyandQoq(@Param("type") Integer type,@Param("mark") Integer mark,@Param("mineCode") String mineCode);
    @Select("<script>" +
            " SELECT \n" +
            "                to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY') AS yearMonth,\n" +
            "                name as companyName,\n" +
            "\t\t\t\t\t\t\t\tmine_code,\n" +
            "                ROUND(output, 0) as output\n" +
            "            FROM (\n" +
            "                SELECT \n" +
            "                    to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "                          name,\n" +
            "\t\t\t\t\t\t\t\t\t\t\t\t\tmine_code,\n" +
            "                    ROUND(SUM(year_quantity) / 10000, 0) AS output \n" +
            "                FROM \n" +
            "                    yield_production\n" +
            "                WHERE \n" +
            "                    date_trunc('year', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 YEAR - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "                    \n" +
            "                    AND data_date  <![CDATA[ >= ]]>   TO_CHAR(NOW() - INTERVAL '10 YEARS', 'YYYY-MM-DD')\n" +
            "                GROUP BY \n" +
            "                    to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD'), name,mine_code\n" +
            "            ) AS subquery1\n" +
            "             where mine_code = #{mineCode}  order by yearMonth" +
            "</script>")
    List<MonthOuputVO> getOutputYoyRecentlyTenYears(@Param("mineCode") String mineCode);

    @Select("<script>" +
            " SELECT \n" +
            "                to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY') AS yearMonth,\n" +
            "                name as companyName,\n" +
            "\t\t\t\t\t\t\t\tmine_code,\n" +
            "                ROUND(output, 0) as output\n" +
            "            FROM (\n" +
            "                SELECT \n" +
            "                    to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "                          name,\n" +
            "\t\t\t\t\t\t\t\t\t\t\t\t\tmine_code,\n" +
            "                    ROUND(SUM(year_total) / 10000, 0) AS output \n" +
            "                FROM \n" +
            "                    group_sale_gather\n" +
            "                WHERE \n" +
            "                    date_trunc('year', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 YEAR - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "                    \n" +
            "                    AND data_date  <![CDATA[ >= ]]>   TO_CHAR(NOW() - INTERVAL '10 YEARS', 'YYYY-MM-DD')\n" +
            "                GROUP BY \n" +
            "                    to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD'), name,mine_code\n" +
            "            ) AS subquery1\n" +
            "             where mine_code = #{mineCode}  order by yearMonth" +
            "</script>")
    List<MonthOuputVO> getSaleYoyRecentlyTenYears(@Param("mineCode") String mineCode);


    @Select("<script>" +
            " SELECT \n" +
            "                 to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY-MM') AS yearMonth,\n" +
            "                mine_code,name as companyName,\n" +
            "                output\n" +
            "            FROM (\n" +
            "                SELECT \n" +
            "                    to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "                    mine_code,name,\n" +
            "                    ROUND(SUM(month_quantity) / 10000, 2) AS output \n" +
            "                FROM \n" +
            "                    yield_production\n" +
            "                WHERE \n" +
            "                    date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "              AND to_date(data_date, 'YYYY-MM-DD')  <![CDATA[ >= ]]>  (CURRENT_DATE - INTERVAL '12 months') \n" +
            "                GROUP BY \n" +
            "                    date_trunc('month', CAST(data_date AS date)), mine_code,name\n" +
            "                ) AS subquery\n" +
            "            where mine_code =  #{mineCode} \n" +
            "            ORDER BY \n" +
            "                yearMonth, companyName; " +
            "</script>")
    List<MonthOuputVO> getOutputYoyrecentlyTwelveMonths(@Param("mineCode") String mineCode);


    @Select("<script>" +
            " SELECT \n" +
            "                 to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY-MM') AS yearMonth,\n" +
            "                mine_code,name as companyName,\n" +
            "                output\n" +
            "            FROM (\n" +
            "                SELECT \n" +
            "                    to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "                    mine_code,name,\n" +
            "                    ROUND(SUM(month_total) / 10000, 2) AS output \n" +
            "                FROM \n" +
            "                    group_sale_gather\n" +
            "                WHERE \n" +
            "                    date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "              AND to_date(data_date, 'YYYY-MM-DD')  <![CDATA[ >= ]]>  (CURRENT_DATE - INTERVAL '12 months') \n" +
            "                GROUP BY \n" +
            "                    date_trunc('month', CAST(data_date AS date)), mine_code,name\n" +
            "                ) AS subquery\n" +
            "            where mine_code =  #{mineCode} \n" +
            "            ORDER BY \n" +
            "                yearMonth, companyName; " +
            "</script>")
    List<MonthOuputVO> getSaleYoyrecentlyTwelveMonths(@Param("mineCode") String mineCode);



    @Select("<script>" +
            " \n" +
            "SELECT \n" +
            "     to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY-MM') AS yearMonth,\n" +
            "    companyName ,\n" +
            "    output\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "        CASE \n" +
            "            WHEN company_code = '91150100767874571R' THEN '北方公司'\n" +
            "            WHEN company_code = '911507001151964613' THEN '蒙东公司'\n" +
            "            ELSE '煤业公司'\n" +
            "        END AS companyName,\n" +
            "        ROUND(SUM(month_quantity) / 10000, 2) AS output \n" +
            "    FROM \n" +
            "        yield_production\n" +
            "    WHERE \n" +
            "        date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "  AND to_date(data_date, 'YYYY-MM-DD') <![CDATA[ >= ]]> (CURRENT_DATE - INTERVAL '12 months') "+
            "    GROUP BY \n" +
            "        date_trunc('month', CAST(data_date AS date)), companyName\n" +
            "    ) AS subquery\n" +
            "UNION ALL\n" +
            "SELECT \n" +
            "     to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY-MM') AS yearMonth,\n" +
            "    '总计' AS group_code,\n" +
            "    ROUND(SUM(output), 2) AS output\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
            "        CASE \n" +
            "            WHEN company_code = '91150100767874571R' THEN '北方公司'\n" +
            "            WHEN company_code = '911507001151964613' THEN '蒙东公司'\n" +
            "            ELSE '煤业公司'\n" +
            "        END AS group_code,\n" +
            "        ROUND(SUM(month_quantity) / 10000, 2) AS output \n" +
            "    FROM \n" +
            "        yield_production\n" +
            "    WHERE \n" +
            "        date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "  AND to_date(data_date, 'YYYY-MM-DD')  <![CDATA[ >= ]]> (CURRENT_DATE - INTERVAL '12 months') "+
            "    GROUP BY \n" +
            "        date_trunc('month', CAST(data_date AS date)), group_code\n" +
            "    ) AS subquery\n" +
            "GROUP BY \n" +
            "    yearMonth\n" +
            "ORDER BY \n" +
            "    yearMonth, companyName;\n" +
            "\n  " +
            "</script>")
    List<MonthOuputVO> recentlyTwelveMonthsByIndustry();

    @Select("<script>" +
            " \n" +
            "WITH data AS (\n" +
            "  SELECT \n" +
            "    to_char(to_date(data_date, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS date,\n" +
            "    CASE \n" +
            "      WHEN company_code = '91150100767874571R' THEN '北方公司'\n" +
            "      WHEN company_code = '911507001151964613' THEN '蒙东公司'\n" +
            "      ELSE '煤业公司'\n" +
            "    END AS companyName,\n" +

            "    ROUND(SUM(today_quantity) / 10000, 2) AS output " +
            "  FROM \n" +
            "    yield_production\n" +
            "  WHERE \n" +
            "    to_date(data_date, 'YYYY-MM-DD') <![CDATA[ > ]]> current_date - interval '31 day'\n" +
            " <if test='flag!= null and flag == 1 '>"+
            " AND to_date(data_date, 'YYYY-MM-DD') <![CDATA[ < ]]> current_date-1 " +
            " </if>"+
            "  GROUP BY \n" +
            "    to_char(to_date(data_date, 'YYYY-MM-DD'), 'YYYY-MM-DD'),\n" +
            "    CASE \n" +
            "      WHEN company_code = '91150100767874571R' THEN '北方公司'\n" +
            "      WHEN company_code = '911507001151964613' THEN '蒙东公司'\n" +
            "      ELSE '煤业公司'\n" +
            "    END\n" +
            ")\n" +
            "SELECT \n" +
            "  --date,\n" +
            "\t COALESCE(date, '30天') AS dates, \n" +
            "  COALESCE(companyName, '总计') AS companyName, \n" +
            "  SUM(output) AS output\n" +
            "FROM data\n" +
            "GROUP BY GROUPING SETS ((date, companyName), (date), ())\n" +
            "ORDER BY date, output,companyName; " +
            "</script>")
    List<MonthOuputVO> recentlyThirtyDaysByIndustry(@Param("flag") Integer flag);
    //包含滇东
    @Select("<script>" +
            " select group_code as companyName,year as yearMonth , ROUND(SUM(total_year_quantity) / 10000, 0) as output from (\n" +
            "            \n" +
            "            select group_code,year,total_year_quantity from (\n" +
            "            WITH data AS (\n" +
            "              SELECT \n" +
            "                DATE(data_date) AS dates,\n" +
            "                CASE \n" +
            "                  WHEN company_code = '91610132311043067B' THEN '陕西矿业'\n" +
            "                  WHEN company_code = '916200002258722645' THEN '华亭煤业'\n" +
            "                  WHEN company_code = '91621000566400003E' THEN '庆阳煤电'\n" +
            "                  WHEN company_code = '91150781720169744B' THEN '扎煤公司'\n" +
            "                  WHEN company_code = '91530325346622296C' THEN '滇东公司'\n" +
            "                  ELSE '其他'\n" +
            "                END AS group_code,\n" +
            "                EXTRACT(YEAR FROM DATE(data_date)) AS year,\n" +
            "                SUM(year_quantity) AS total_year_quantity\n" +
            "              FROM \n" +
            "                yield_production\n" +
            "              WHERE \n" +
            "            company_code in('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
            "            and\n" +
            "                   date_trunc('year', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 YEAR - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "            \n" +
            "              GROUP BY \n" +
            "                DATE(data_date),\n" +
            "                CASE \n" +
            "                  WHEN company_code = '91610132311043067B' THEN '陕西矿业'\n" +
            "                  WHEN company_code = '916200002258722645' THEN '华亭煤业'\n" +
            "            WHEN company_code = '91621000566400003E' THEN '庆阳煤电'\n" +
            "            WHEN company_code = '91150781720169744B' THEN '扎煤公司'\n" +
            "            WHEN company_code = '91530325346622296C' THEN '滇东公司'\n" +
            "                  ELSE '其他'\n" +
            "                END\n" +
            "            )\n" +
            "            SELECT * FROM data\n" +
            "            UNION ALL\n" +
            "            SELECT \n" +
            "              dates,\n" +
            "              '总计' AS group_code,\n" +
            "              year,\n" +
            "               ROUND(SUM(total_year_quantity), 0) AS total_year_quantity\n" +
            "            FROM data\n" +
            "            GROUP BY dates, year\n" +
            "            ORDER BY dates,total_year_quantity asc) a\n" +
            "            \n" +
            "            \n" +
            "            \n" +
            "            union all\n" +
            "            \n" +
            "            SELECT \n" +
            "              a.group_code,\n" +
            "              EXTRACT(YEAR FROM DATE_TRUNC('year', CURRENT_DATE)) - offs AS year,\n" +
            "              0\n" +
            "            FROM generate_series(1, 10) AS offs  \n" +
            "            CROSS JOIN (\n" +
            "              SELECT  '陕西矿业' AS group_code\n" +
            "              UNION ALL\n" +
            "              SELECT  '华亭煤业'\n" +
            "              UNION ALL\n" +
            "              SELECT  '庆阳煤电'\n" +
            "              UNION ALL\n" +
            "              SELECT  '扎煤公司'\n" +
            "              UNION ALL\n" +
            "              SELECT  '滇东公司'\n" +
            "            ) a\n" +
            "            ) a group by group_code,year\n" +
            "            order by year " +
            "</script>")
//不包含滇东公司
//    @Select("<script>" +
//            " SELECT \n" +
//            "    to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY') AS yearMonth,\n" +
//            "    companyName,\n" +
//            "    output\n" +
//            "FROM (\n" +
//            "    SELECT \n" +
//            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
//            "             CASE \n" +
//            "                 WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
//            "                 WHEN company_code = '916200002258722645' THEN '华亭'\n" +
//            "                 WHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
//            "                 WHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
//            "                 WHEN company_code = '91530325346622296C' THEN '滇东'\n" +
//            "               END AS companyName,\n" +
//            "        ROUND(SUM(year_quantity) / 10000, 2) AS output \n" +
//            "    FROM \n" +
//            "        yield_production\n" +
//            "    WHERE \n" +
//            "        date_trunc('year', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 YEAR - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
//            "        and company_code in('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
//            "        AND data_date <![CDATA[ >= ]]> TO_CHAR(NOW() - INTERVAL '10 YEARS', 'YYYY-MM-DD')\n" +
//            "    GROUP BY \n" +
//            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD'), companyName\n" +
//            ") AS subquery1\n" +
//            "UNION ALL\n" +
//            "SELECT \n" +
//            "    to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY') AS yearMonth,\n" +
//            "    '总计' AS companyName,\n" +
//            "    ROUND(SUM(output), 2) AS output\n" +
//            "FROM (\n" +
//            "    SELECT \n" +
//            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
//            "               CASE \n" +
//            "                 WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
//            "                 WHEN company_code = '916200002258722645' THEN '华亭'\n" +
//            "                 WHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
//            "                 WHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
//            "                 WHEN company_code = '91530325346622296C' THEN '滇东'\n" +
//            "               END AS companyName,\n" +
//            "        ROUND(SUM(year_quantity) / 10000, 2) AS output \n" +
//            "    FROM \n" +
//            "        yield_production\n" +
//            "    WHERE \n" +
//            "        date_trunc('year', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 YEAR - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
//            "        and company_code in('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
//            "        AND data_date  <![CDATA[ >= ]]> TO_CHAR(NOW() - INTERVAL '10 YEARS', 'YYYY-MM-DD')\n" +
//            "    GROUP BY \n" +
//            "        to_char(date_trunc('year', CAST(data_date AS date)) + INTERVAL '1 YEAR - 1 DAY', 'YYYY-MM-DD'), companyName\n" +
//            ") AS subquery2\n" +
//            "GROUP BY \n" +
//            "    yearMonth\n" +
//            "ORDER BY \n" +
//            "    yearMonth, companyName;\n " +
//            "</script>")
    List<MonthOuputVO> recentlyTenYearsByCompany();
    //包含滇东
    @Select("<script>" +
            " SELECT to_char(to_date(month, 'YYYY-MM'), 'YYYY-MM')  as yearMonth,group_code as companyName,ROUND(SUM(total_month_quantity) / 10000, 2) as output FROM (\n" +
            "            \n" +
            "             \n" +
            "              SELECT \n" +
            "\t\t\t\t\t\t\t\n" +
            "                to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS month,\n" +
            "                CASE \n" +
            "                  WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
            "                  WHEN company_code = '916200002258722645' THEN '华亭'\n" +
            "                  WHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
            "                  WHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
            "                  WHEN company_code = '91530325346622296C' THEN '滇东'\n" +
            "                  ELSE '其他'\n" +
            "                END AS group_code,\n" +
            "                SUM(month_quantity::numeric) AS total_month_quantity\n" +
            "              FROM \n" +
            "                yield_production c\n" +
            "              WHERE \n" +
            "\t\t\t\t\t\t\t\n" +
            "               date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "                AND company_code IN ('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
            "\t\t\t\t\t\t\t\t  AND to_date(data_date, 'YYYY-MM-DD') <![CDATA[ >= ]]>   (CURRENT_DATE - INTERVAL '12 months')\n" +
            "              GROUP BY \n" +
            "                --to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD'),\n" +
            "\t\t\t\t\t\t\t\t  date_trunc('month', CAST(data_date AS date)),\n" +
            "                CASE \n" +
            "                  WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
            "                  WHEN company_code = '916200002258722645' THEN '华亭'\n" +
            "                  WHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
            "                  WHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
            "                  WHEN company_code = '91530325346622296C' THEN '滇东'\n" +
            "                  ELSE '其他'\n" +
            "                END\n" +
            "              \n" +
            "              UNION ALL\n" +
            "              \n" +
            "              SELECT \n" +
            "                to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS month,\n" +
            "                '总计' AS group_code,\n" +
            "                SUM(month_quantity::numeric) AS total_month_quantity\n" +
            "              FROM \n" +
            "                yield_production b\n" +
            "              WHERE \n" +
            "                date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
            "                AND company_code IN ('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
            "              GROUP BY \n" +
            "                to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD')\n" +
            "\t\t\t\t\t\t\t\n" +
            "             UNION ALL\n" +
            "            \n" +
            "            SELECT \n" +
            "\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\tto_char(date_trunc('month', m.month) + interval '1 month - 1 day', 'YYYY-MM-DD') AS month,\n" +
            "                   \n" +
            "                   a.group_code,\n" +
            "                   0\n" +
            "            FROM generate_series(\n" +
            "              date_trunc('month', current_date - interval '13 months'), " +
            "    date_trunc('month', current_date - interval '1 month'),\n" +
            "              interval '1 month'\n" +
            "            ) m(month)\n" +
            "            CROSS JOIN (\n" +
            "              SELECT '91610132311043067B' AS company_code, '陕西' AS group_code\n" +
            "              UNION ALL\n" +
            "              SELECT '916200002258722645', '华亭'\n" +
            "              UNION ALL\n" +
            "              SELECT '91621000566400003E', '庆阳'\n" +
            "              UNION ALL\n" +
            "              SELECT '91150781720169744B', '扎煤'\n" +
            "              UNION ALL\n" +
            "              SELECT '91530325346622296C', '滇东'\n" +
            "            ) a\n" +
            "            \n" +
            "                 \n" +
            "            \n" +
            "            ) a \n" +
            "            WHERE COALESCE(to_date(month, 'YYYY-MM-DD'), current_date)  <![CDATA[ >= ]]>  current_date - interval '12 month'\n" +
            "            group by month,group_code\n" +
            "            ORDER BY month,group_code  " +
            "</script>")
//不包含滇东公司
//    @Select("<script>" +
//            " SELECT \n" +
//            "                 to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY-MM') AS yearMonth,\n" +
//            "                companyName ,\n" +
//            "                output\n" +
//            "            FROM (\n" +
//            "                SELECT \n" +
//            "                    to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
//            "                    CASE \n" +
//            "                         WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
//            "                  WHEN company_code = '916200002258722645' THEN '华亭'\n" +
//            "                  WHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
//            "                  WHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
//            "                  WHEN company_code = '91530325346622296C' THEN '滇东'\n" +
//            "                    END AS companyName,\n" +
//            "                    ROUND(SUM(month_quantity) / 10000, 2) AS output \n" +
//            "                FROM \n" +
//            "                    yield_production\n" +
//            "                WHERE \n" +
//            "                    date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
//            "\t\t\t\t\t\t\t\t\t\t\n" +
//            "\t\t\t\t\t\t\t\t\t\t AND company_code IN ('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
//            "\t\t\t\t\t\t\t\t\t\t AND to_date(data_date, 'YYYY-MM-DD') <![CDATA[ >= ]]> (CURRENT_DATE - INTERVAL '12 months')\n" +
//            "                GROUP BY \n" +
//            "                    date_trunc('month', CAST(data_date AS date)), companyName\n" +
//            "                ) AS subquery\n" +
//            "            UNION ALL\n" +
//            "            SELECT \n" +
//            "                 to_char(date_trunc('month', CAST(yearMonth AS date)), 'YYYY-MM') AS yearMonth,\n" +
//            "                '总计' AS group_code,\n" +
//            "                ROUND(SUM(output), 2) AS output\n" +
//            "            FROM (\n" +
//            "                SELECT \n" +
//            "                    to_char(date_trunc('month', CAST(data_date AS date)) + INTERVAL '1 MONTH - 1 DAY', 'YYYY-MM-DD') AS yearMonth,\n" +
//            "                    CASE \n" +
//            "                               WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
//            "                  WHEN company_code = '916200002258722645' THEN '华亭'\n" +
//            "                  WHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
//            "                  WHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
//            "                  WHEN company_code = '91530325346622296C' THEN '滇东'\n" +
//            "                    END AS group_code,\n" +
//            "                    ROUND(SUM(month_quantity) / 10000, 2) AS output \n" +
//            "                FROM \n" +
//            "                    yield_production\n" +
//            "                WHERE \n" +
//            "                    date_trunc('month', to_date(data_date, 'YYYY-MM-DD')) + INTERVAL '1 MONTH - 1 DAY' = to_date(data_date, 'YYYY-MM-DD')\n" +
//            "\t\t\t\t\t\t\t\t\t\t AND company_code IN ('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
//            "\t\t\t\t\t\t\t\t\t\t AND to_date(data_date, 'YYYY-MM-DD') <![CDATA[ >= ]]> (CURRENT_DATE - INTERVAL '12 months')\n" +
//            "                GROUP BY \n" +
//            "                    date_trunc('month', CAST(data_date AS date)), group_code\n" +
//            "                ) AS subquery\n" +
//            "            GROUP BY \n" +
//            "                yearMonth\n" +
//            "            ORDER BY \n" +
//            "                yearMonth, companyName;  " +
//            "</script>")
    List<MonthOuputVO> recentlyTwelveMonthsByCompany();
    @Select("<script>" +
            " \n" +
            "\n" +
            "select dates," +
            " group_code as companyName,ROUND(SUM(total_month_quantity) / 10000, 2) AS  output from (select dates,group_code,total_month_quantity from (WITH data AS (\n" +
            "  SELECT \n" +
            "    to_char(to_date(data_date, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS date,\n" +
            "    CASE \n" +
            "      WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
            "      WHEN company_code = '916200002258722645' THEN '华亭'\n" +
            "\t\t\tWHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
            "\t\t\tWHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
            "\t\t\tWHEN company_code = '91530325346622296C' THEN '滇东'\n" +
            "      ELSE '其他'\n" +
            "    END AS group_code,\n" +
            "    SUM(today_quantity::numeric) AS total_month_quantity\n" +
            "  FROM \n" +
            "    yield_production\n" +
            "  WHERE \n" +
            "    to_date(data_date, 'YYYY-MM-DD') <![CDATA[ > ]]>  current_date - interval '31 day'\n" +
            " <if test='flag!= null and flag == 1 '>"+
            "  and to_date(data_date, 'YYYY-MM-DD') <![CDATA[ < ]]> current_date-1   " +
            " </if>"+
            "\t\t\tand company_code in('91610132311043067B','916200002258722645','91621000566400003E','91150781720169744B','91530325346622296C')\n" +
            "  GROUP BY \n" +
            "    to_char(to_date(data_date, 'YYYY-MM-DD'), 'YYYY-MM-DD'),\n" +
            "    CASE \n" +
            "      WHEN company_code = '91610132311043067B' THEN '陕西'\n" +
            "      WHEN company_code = '916200002258722645' THEN '华亭'\n" +
            "\t\t\tWHEN company_code = '91621000566400003E' THEN '庆阳'\n" +
            "\t\t\tWHEN company_code = '91150781720169744B' THEN '扎煤'\n" +
            "\t\t\tWHEN company_code = '91530325346622296C' THEN '滇东'\n" +
            "      ELSE '其他'\n" +
            "    END\n" +
            ")\n" +
            "SELECT \n" +
            "  --date,\n" +
            "\t COALESCE(date, '30天') AS dates, \n" +
            "  COALESCE(group_code, '总计') AS group_code, \n" +
            "  COALESCE(SUM(total_month_quantity),0) AS total_month_quantity\n" +
            "FROM data\n" +
            "GROUP BY GROUPING SETS ((date, group_code), (date), ())\n" +
            "ORDER BY date, total_month_quantity,group_code\n" +
            ") a\n" +
            "\n" +
            "UNION all\n" +
            "SELECT to_char(date_trunc('day', current_date - offs), 'YYYY-MM-DD') AS dates,\n" +
            "       group_code,\n" +
            "       0\n" +
            "FROM generate_series(1, 30) AS offs\n" +
            "CROSS JOIN (\n" +
            "  SELECT '91610132311043067B' AS company_code, '陕西' AS group_code\n" +
            "  UNION ALL\n" +
            "  SELECT '916200002258722645', '华亭'\n" +
            "  UNION ALL\n" +
            "  SELECT '91621000566400003E', '庆阳'\n" +
            "  UNION ALL\n" +
            "  SELECT '91150781720169744B', '扎煤'\n" +
            "  UNION ALL\n" +
            "  SELECT '91530325346622296C', '滇东'\n" +
            ")  b \n" +

            " <if test='flag!= null and flag == 1 '>"+
            "  WHERE to_char(date_trunc('day', current_date - offs), 'YYYY-MM-DD') <![CDATA[ < ]]> to_char(current_date-1, 'YYYY-MM-DD')  " +
            " </if>"+
            ") a\n" +
            "group by dates,group_code\n" +
            "ORDER BY dates,group_code \n" +
            "\n" +
            "\n " +
            "</script>")
    List<MonthOuputVO> recentlyThirtyDaysByCompany(@Param("flag") Integer flag);


    @Select("<script>" +
            " SELECT \n" +
            "    name as companyName, \n" +
            "\t\t to_char(data_date::date, 'DD') AS yearMonth,\n" +
            "    data_date as dates,\n" +
            "    COALESCE(today_footage, 0) AS output\n" +
            "FROM group_footage\n" +
            "WHERE mine_code = #{mineCode} " +
            "    AND data_date <![CDATA[ >= ]]> to_char(CURRENT_DATE - '30 day'::INTERVAL, 'YYYY-MM-DD') \n" +
            "    AND data_date <![CDATA[ <= ]]> to_char(CURRENT_DATE - '1 day'::INTERVAL, 'YYYY-MM-DD')  " +
            "    AND type = #{type} order by data_date; " +
            "</script>")
    List<MonthOuputVO> getMonthlyTrendOfExcavation(@Param("mineCode") String mineCode,@Param("type") String type);


    @Select("<script>" +
            " SELECT \n" +
            "    name as mineName, \n" +
            "    mine_code as mineCode, \n" +
            "    COALESCE(month_plan, 0) AS plan,\n" +
            "    COALESCE(month_footage, 0) AS monthCount,\n" +
            "    COALESCE(year_plan, 0) AS yearPlan,\n" +
            "    COALESCE(year_footage, 0) AS yearCount,\n" +
            "    data_date as dataDate" +
            " FROM group_footage\n" +
            " WHERE  " +
            " <if test='flag!= null and flag == 1 '>"+
            "  data_date = to_char(CURRENT_DATE - '2 day'::INTERVAL, 'YYYY-MM-DD')  " +
            " </if>"+
            " <if test='flag== null and flag != 1 '>"+
            "  data_date = to_char(CURRENT_DATE - '1 day'::INTERVAL, 'YYYY-MM-DD')  " +
            " </if>"+
            "    AND type = #{type}; " +
            "</script>")
    List<SaleAnalyseVo> compareYearMonthPlanActual(@Param("type") String type,@Param("flag") Integer flag);
    @Select("<script>" +
            " SELECT \n" +
            "    ROUND(SUM(year_plan), 0) AS yearPlan,\n" +
            "    ROUND(SUM(year_footage), 0) AS yearCumulativeCompletion,\n" +
            "    ROUND((SUM(year_footage) / SUM(year_plan)) * 100, 2) AS completionRate,\n" +
            "    ROUND(SUM(month_plan), 0) AS planOutput,\n" +
            "    ROUND(SUM(month_footage), 0) AS footageMonth,\n" +
            "    ROUND((SUM(month_footage) / SUM(month_plan)) * 100, 2) AS output,\n" +
            "    CASE \n" +
            "        WHEN EXTRACT(DAY FROM CURRENT_DATE) = 1 THEN\n" +
            "            ROUND((SUM(month_plan) / EXTRACT(DAY FROM (DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '1 DAY')::DATE))* 1, 0)\n" +
            "        ELSE\n" +
            "            ROUND((SUM(month_plan) / EXTRACT(DAY FROM DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month') - DATE_TRUNC('month', CURRENT_DATE)))* 1, 0)\n" +
            "    END AS planOutPuts,\n" +
            "    ROUND(SUM(today_footage), 0) AS todayQuantity,\n" +
            "    ROUND((SUM(today_footage) / CASE \n" +
            "                                    WHEN EXTRACT(DAY FROM CURRENT_DATE) = 1 THEN\n" +
            "                                        (SUM(month_plan) / EXTRACT(DAY FROM (DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '1 DAY')::DATE))\n" +
            "                                    ELSE\n" +
            "                                        (SUM(month_plan) / EXTRACT(DAY FROM DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month') - DATE_TRUNC('month', CURRENT_DATE)))\n" +
            "                                END) * 100, 2) AS avgOutput\n" +
            "FROM \n" +
            "    group_footage\n" +
            "WHERE \n" +
            " <if test='flag!= null and flag == 1 '>"+
            "  data_date = to_char(CURRENT_DATE - '2 day'::INTERVAL, 'YYYY-MM-DD')\n  " +
            " </if>"+
            " <if test='flag== null and flag != 1 '>"+
            "  data_date = to_char(CURRENT_DATE - '1 day'::INTERVAL, 'YYYY-MM-DD')\n  " +
            " </if>"+
//            "    data_date = to_char(CURRENT_DATE - '1 day'::INTERVAL, 'YYYY-MM-DD') \n" +
            "    AND type = #{type}; " +
            "</script>")
    List<MonthYieldVo> getDrivingStrippingStatus(@Param("type") String type,@Param("flag") Integer flag);


    @Select("<script>" +
            " SELECT SUM(month_plan),\n" +
            "  ROUND((SUM(month_plan)  / EXTRACT(DAY FROM DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month') - DATE_TRUNC('month', CURRENT_DATE))) * 1 /10000, 2) AS planOutputDay,\n" +
            " ROUND(SUM(today_quantity) / 10000, 0) AS todayQuantity ,\n" +
            "  ROUND((SUM(today_quantity) / (SUM(month_plan)  /  EXTRACT(DAY FROM DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month') - DATE_TRUNC('month', CURRENT_DATE) ))) * 100, 2) AS completionRateDay\n" +
            " FROM yield_production \n" +
            " WHERE " +

            " <if test='flag!= null and flag == 1 '>"+
            "  data_date = to_char(CURRENT_DATE - '2 day'::INTERVAL, 'YYYY-MM-DD')\n  " +
            " </if>"+

            " <if test='flag== null and flag != 1 '>"+
            "  data_date = to_char(CURRENT_DATE - '1 day'::INTERVAL, 'YYYY-MM-DD')\n  " +
            " </if>"+
            " GROUP BY data_date ; " +
            "</script>")
    List<MonthYieldVo> getOutputStatus(@Param("flag") Integer flag);

    List<repertoryInfo> inventoryCount();

    List<repertoryInfo> inventoryInfo();


    @Insert("INSERT INTO environment_ranking (date, score, rank, id, sysOrgCode_dictText,departNameAbbr, totalScore, createBy, hide, createTime, underreporting, sysOrgCode, remarks) " +
            "VALUES (#{date}, #{score}, #{rank}, #{id}, #{sysOrgCodeDictText}, #{departNameAbbr}, #{totalScore}, #{createBy}, #{hide}, #{createTime}, #{underreporting}, #{sysOrgCode}, #{remarks})")
    void insertAssessment(EnvironmentRanking assessment);
    @Delete("delete from environment_ranking")
    void deleteAssessment();

    @Insert("INSERT INTO efficient_transmission (online_time_total, effective_rate, sys_org_code, sys_org_code_dict_text) " +
            "VALUES (#{onlineTimeTotal}, #{effectiveRate}, #{sysOrgCode}, #{sysOrgCodeDictText})")
    void insertEfficientTransmission(EfficientTransmission assessment);

    @Delete("delete from efficient_transmission")
    void deleteEfficientTransmission( );
}
